package com.scuvc.dao.impl;

import com.scuvc.dao.CarInfoDao;
import com.scuvc.entity.*;
import com.scuvc.utils.ConnectionFactory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CarInfoDaoImpl implements CarInfoDao {
    private QueryRunner queryRunner = new QueryRunner
                        (ConnectionFactory.getDruidDataSource());
    @Override
    public List<CarInfo> getAll() {
        String sql = "SELECT car.id,car.carNo,brand.brand_name,model.model_name,cate.category_name," +
                "car.displacement,car.price,car.car_amount,car.deposit,car.status,car.usable " +
                "FROM eh_carinfo car INNER JOIN eh_categoryinfo cate " +
                "ON car.category_id = cate.id INNER JOIN eh_modelinfo model " +
                "ON car.model_id = model.id INNER JOIN eh_brandinfo brand " +
                "ON car.brand_id = brand.id " +
                "ORDER BY 1";
        List<CarInfo> list = null ;
        try {
            list = queryRunner.query(sql,
                    new ResultSetHandler<List<CarInfo>>() {
                        @Override
                        public List<CarInfo> handle(ResultSet rs) throws SQLException {
                            List<CarInfo> list = new ArrayList<>();
                            setResultSet(rs,list);
                            return list;
                        }
                    });
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    private void setResultSet(ResultSet rs,List<CarInfo> list)throws SQLException {
        while(rs.next()) {
            CarInfo carInfo = new CarInfo();
            carInfo.setId(rs.getLong("id"));
            carInfo.setCarNo(rs.getString("carNo"));

            BrandInfo brandInfo = new BrandInfo();
            brandInfo.setBrand_name(rs.getString("brand_name"));
            carInfo.setBrand(brandInfo);

            ModelInfo modelInfo = new ModelInfo();
            modelInfo.setModel_name(rs.getString("model_name"));
            carInfo.setModel(modelInfo);

            CategoryInfo categoryInfo = new CategoryInfo();
            categoryInfo.setCategory_name(rs.getString("category_name"));
            carInfo.setCategory(categoryInfo);

            carInfo.setDisplacement(rs.getString("displacement"));
            carInfo.setPrice(rs.getLong("price"));
            carInfo.setCar_amount(rs.getDouble("car_amount"));
            carInfo.setDeposit(rs.getLong("deposit"));
            carInfo.setStatus(rs.getInt("status"));
            carInfo.setUsable(rs.getInt("usable"));
            list.add(carInfo);
        }
    }
    @Override
    public List<CarInfo> getAllIsUser() {
       String sql = "SELECT car.id,car.carNo,brand.brand_name,model.model_name,cate.category_name," +
                "car.displacement,car.price,car.car_amount,car.deposit,car.status,car.usable " +
                "FROM eh_carinfo car INNER JOIN eh_categoryinfo cate " +
                "ON car.category_id = cate.id INNER JOIN eh_modelinfo model " +
                "ON car.model_id = model.id INNER JOIN eh_brandinfo brand " +
                "ON car.brand_id = brand.id WHERE car.usable=1 " +
                "ORDER BY 1";
        List<CarInfo> list = null ;
        try {
            list = queryRunner.query(sql,
                    new ResultSetHandler<List<CarInfo>>() {
                        @Override
                        public List<CarInfo> handle(ResultSet rs) throws SQLException {
                            List<CarInfo> list = new ArrayList<>();
                            setResultSet(rs,list);
                            return list;
                        }
                    });
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<CarInfo> getAllPage(int pageNumber, int pageSize) {
        int offset = (pageNumber -1 )*pageSize;
        String sql = "SELECT car.id,car.carNo,brand.brand_name,model.model_name,cate.category_name," +
                "car.displacement,car.price,car.car_amount,car.deposit,car.status,car.usable " +
                "FROM eh_carinfo car INNER JOIN eh_categoryinfo cate " +
                "ON car.category_id = cate.id INNER JOIN eh_modelinfo model " +
                "ON car.model_id = model.id INNER JOIN eh_brandinfo brand " +
                "ON car.brand_id = brand.id " +
                "ORDER BY 1 LIMIT ?,?";
        List<CarInfo> list = null ;
        try {
            list = queryRunner.query(sql, new ResultSetHandler<List<CarInfo>>() {
                @Override
                public List<CarInfo> handle(ResultSet rs) throws SQLException {
                    List<CarInfo> list = new ArrayList<>();
                    setResultSet(rs,list);
                    return list;
                }
            }, offset, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list ;
    }

    @Override
    public List<CarInfo> getAllPageIsUser(int pageNumber, int pageSize) {
        int offset = (pageNumber -1 )*pageSize;
        String sql = "SELECT car.id,car.carNo,brand.brand_name,model.model_name,cate.category_name," +
                "car.displacement,car.price,car.car_amount,car.deposit,car.status,car.usable " +
                "FROM eh_carinfo car INNER JOIN eh_categoryinfo cate " +
                "ON car.category_id = cate.id INNER JOIN eh_modelinfo model " +
                "ON car.model_id = model.id INNER JOIN eh_brandinfo brand " +
                "ON car.brand_id = brand.id WHERE car.usable=1 " +
                "ORDER BY 1 LIMIT ?,?";
        List<CarInfo> list = null ;
        try {
            list = queryRunner.query(sql, new ResultSetHandler<List<CarInfo>>() {
                @Override
                public List<CarInfo> handle(ResultSet rs) throws SQLException {
                    List<CarInfo> list = new ArrayList<>();
                    setResultSet(rs,list);
                    return list;
                }
            }, offset, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list ;
    }

    @Override
    public String getCarInfonByCarNo(String carNo) {
        String sql = "SELECT carNo FROM eh_carInfo WHERE carNo=?";
        try {
            return queryRunner.query(sql,new ScalarHandler<>(),carNo);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null ;
    }

    @Override
    public int insert(CarInfo carInfo) {
        String sql = "INSERT INTO eh_carInfo VALUES(NULL,?,?,?,?,?,?,?,?,1,1)";
        try {
            return queryRunner.update(sql,carInfo.getCarNo(),carInfo.getBrand().getId(),
                    carInfo.getModel().getId(),carInfo.getCategory().getId(),
                    carInfo.getDisplacement(),carInfo.getPrice(),carInfo.getCar_amount(),
                    carInfo.getDeposit());
        } catch (SQLException e) {
            e.printStackTrace();
        };
        return 0;
    }

    @Override
    public List<CarInfo> getCarInfoByCarNo(String carNo) {
        String sql = "SELECT car.id,car.carNo,brand.brand_name,model.model_name,cate.category_name," +
                "car.displacement,car.price,car.car_amount,car.deposit,car.status,car.usable " +
                "FROM eh_carinfo car INNER JOIN eh_categoryinfo cate " +
                "ON car.category_id = cate.id INNER JOIN eh_modelinfo model " +
                "ON car.model_id = model.id INNER JOIN eh_brandinfo brand " +
                "ON car.brand_id = brand.id WHERE car.carNo=?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<List<CarInfo>>() {
                @Override
                public List<CarInfo> handle(ResultSet rs) throws SQLException {
                    List<CarInfo> list = new ArrayList<>();
                    setResultSet(rs,list);
                    return list;
                }
            }, carNo);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null ;
    }

    @Override
    public List<CarInfo> getCarInfoByBrandId(Long id) {
        String sql = "SELECT car.id,car.carNo,brand.brand_name,model.model_name,cate.category_name," +
                "car.displacement,car.price,car.car_amount,car.deposit,car.status,car.usable " +
                "FROM eh_carinfo car INNER JOIN eh_categoryinfo cate " +
                "ON car.category_id = cate.id INNER JOIN eh_modelinfo model " +
                "ON car.model_id = model.id INNER JOIN eh_brandinfo brand " +
                "ON car.brand_id = brand.id WHERE car.brand_id=?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<List<CarInfo>>() {
                @Override
                public List<CarInfo> handle(ResultSet rs) throws SQLException {
                    List<CarInfo> list = new ArrayList<>();
                    setResultSet(rs,list);
                    return list;
                }
            }, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<CarInfo> getCarInfoByModelId(Long id) {
        String sql = "SELECT car.id,car.carNo,brand.brand_name,model.model_name,cate.category_name," +
                "car.displacement,car.price,car.car_amount,car.deposit,car.status,car.usable " +
                "FROM eh_carinfo car INNER JOIN eh_categoryinfo cate " +
                "ON car.category_id = cate.id INNER JOIN eh_modelinfo model " +
                "ON car.model_id = model.id INNER JOIN eh_brandinfo brand " +
                "ON car.brand_id = brand.id WHERE car.model_id=?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<List<CarInfo>>() {
                @Override
                public List<CarInfo> handle(ResultSet rs) throws SQLException {
                    List<CarInfo> list = new ArrayList<>();
                    setResultSet(rs,list);
                    return list;
                }
            }, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<CarInfo> getCarInfoByCategroyId(Long id) {
        String sql = "SELECT car.id,car.carNo,brand.brand_name,model.model_name,cate.category_name," +
                "car.displacement,car.price,car.car_amount,car.deposit,car.status,car.usable " +
                "FROM eh_carinfo car INNER JOIN eh_categoryinfo cate " +
                "ON car.category_id = cate.id INNER JOIN eh_modelinfo model " +
                "ON car.model_id = model.id INNER JOIN eh_brandinfo brand " +
                "ON car.brand_id = brand.id WHERE car.category_id=?";
        try {
            return queryRunner.query(sql, new ResultSetHandler<List<CarInfo>>() {
                @Override
                public List<CarInfo> handle(ResultSet rs) throws SQLException {
                    List<CarInfo> list = new ArrayList<>();
                    setResultSet(rs,list);
                    return list;
                }
            }, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int updateCarInfo(String carNo, Long price, Double car_amount, Long deposit, Integer status,
                             int usable) {
        String sql = "UPDATE eh_carInfo SET price=?,car_amount=?,deposit=?,status=?,usable=? " +
                "WHERE carNo=?";
        try {
            return queryRunner.update(sql,price,car_amount,deposit,status,usable,carNo);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static void main(String[] args) {
        CarInfoDaoImpl cd = new CarInfoDaoImpl();
       List<CarInfo> list = cd.getCarInfoByCategroyId(1L);
        for(CarInfo c:list){
            System.out.println(c);
        }
    }
}
